{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "view-in-github"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/sugarforever/LangChain-SQL-Chain/blob/main/Chat_with_SQL_Database.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 39,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4HHQlehr2szs",
        "outputId": "c775de95-54ba-4b2d-dc19-23e4866c56c1"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Requirement already satisfied: langchain in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (0.0.139)\n",
            "Requirement already satisfied: async-timeout<5.0.0,>=4.0.0 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from langchain) (4.0.2)\n",
            "Requirement already satisfied: PyYAML>=5.4.1 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from langchain) (5.4.1)\n",
            "Requirement already satisfied: requests<3,>=2 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from langchain) (2.28.2)\n",
            "Requirement already satisfied: numpy<2,>=1 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from langchain) (1.23.5)\n",
            "Requirement already satisfied: aiohttp<4.0.0,>=3.8.3 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from langchain) (3.8.4)\n",
            "Requirement already satisfied: openapi-schema-pydantic<2.0,>=1.2 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from langchain) (1.2.4)\n",
            "Requirement already satisfied: dataclasses-json<0.6.0,>=0.5.7 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from langchain) (0.5.7)\n",
            "Requirement already satisfied: tenacity<9.0.0,>=8.1.0 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from langchain) (8.2.2)\n",
            "Requirement already satisfied: gptcache>=0.1.7 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from langchain) (0.1.10)\n",
            "Requirement already satisfied: pydantic<2,>=1 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from langchain) (1.10.7)\n",
            "Requirement already satisfied: SQLAlchemy<2,>=1 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from langchain) (1.4.47)\n",
            "Requirement already satisfied: frozenlist>=1.1.1 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain) (1.3.3)\n",
            "Requirement already satisfied: multidict<7.0,>=4.5 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain) (6.0.4)\n",
            "Requirement already satisfied: charset-normalizer<4.0,>=2.0 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain) (3.1.0)\n",
            "Requirement already satisfied: attrs>=17.3.0 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain) (22.2.0)\n",
            "Requirement already satisfied: aiosignal>=1.1.2 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain) (1.3.1)\n",
            "Requirement already satisfied: yarl<2.0,>=1.0 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain) (1.8.2)\n",
            "Requirement already satisfied: marshmallow<4.0.0,>=3.3.0 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from dataclasses-json<0.6.0,>=0.5.7->langchain) (3.19.0)\n",
            "Requirement already satisfied: typing-inspect>=0.4.0 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from dataclasses-json<0.6.0,>=0.5.7->langchain) (0.8.0)\n",
            "Requirement already satisfied: marshmallow-enum<2.0.0,>=1.5.1 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from dataclasses-json<0.6.0,>=0.5.7->langchain) (1.5.1)\n",
            "Requirement already satisfied: cachetools in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from gptcache>=0.1.7->langchain) (5.3.0)\n",
            "Requirement already satisfied: openai in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from gptcache>=0.1.7->langchain) (0.27.4)\n",
            "Requirement already satisfied: typing-extensions>=4.2.0 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from pydantic<2,>=1->langchain) (4.5.0)\n",
            "Requirement already satisfied: idna<4,>=2.5 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from requests<3,>=2->langchain) (3.4)\n",
            "Requirement already satisfied: urllib3<1.27,>=1.21.1 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from requests<3,>=2->langchain) (1.26.15)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from requests<3,>=2->langchain) (2022.12.7)\n",
            "Requirement already satisfied: greenlet!=0.4.17 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from SQLAlchemy<2,>=1->langchain) (2.0.2)\n",
            "Requirement already satisfied: packaging>=17.0 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from marshmallow<4.0.0,>=3.3.0->dataclasses-json<0.6.0,>=0.5.7->langchain) (23.0)\n",
            "Requirement already satisfied: mypy-extensions>=0.3.0 in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from typing-inspect>=0.4.0->dataclasses-json<0.6.0,>=0.5.7->langchain) (1.0.0)\n",
            "Requirement already satisfied: tqdm in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (from openai->gptcache>=0.1.7->langchain) (4.65.0)\n",
            "Note: you may need to restart the kernel to use updated packages.\n",
            "Requirement already satisfied: pymysql in /Users/wyang14/.pyenv/versions/3.9.16/lib/python3.9/site-packages (1.0.3)\n",
            "Note: you may need to restart the kernel to use updated packages.\n"
          ]
        }
      ],
      "source": [
        "%pip install langchain\n",
        "%pip install pymysql"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 40,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-B24JEue3Zwz",
        "outputId": "952e66fc-12e6-480d-8068-0fdfc5ed4baf"
      },
      "outputs": [],
      "source": [
        "from langchain import OpenAI, SQLDatabase, SQLDatabaseChain"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 41,
      "metadata": {},
      "outputs": [],
      "source": [
        "db = SQLDatabase.from_uri(\"mysql+pymysql://root:root@127.0.0.1/students\")\n",
        "llm = OpenAI(temperature=0)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 42,
      "metadata": {},
      "outputs": [],
      "source": [
        "db_chain = SQLDatabaseChain(llm=llm, database=db, verbose=True)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 43,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
            "How many students are there? \n",
            "SQLQuery:\u001b[32;1m\u001b[1;3m SELECT COUNT(*) FROM students;\u001b[0m\n",
            "SQLResult: \u001b[33;1m\u001b[1;3m[(5,)]\u001b[0m\n",
            "Answer:\u001b[32;1m\u001b[1;3m There are 5 students.\u001b[0m\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "data": {
            "text/plain": [
              "' There are 5 students.'"
            ]
          },
          "execution_count": 43,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "db_chain.run(\"How many students are there?\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 44,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
            "What are the students names? \n",
            "SQLQuery:\u001b[32;1m\u001b[1;3m SELECT name FROM students \u001b[0m\n",
            "SQLResult: \u001b[33;1m\u001b[1;3m[('Alex',), ('Alice',), ('Jack',), ('Ophelia',), ('Zack',)]\u001b[0m\n",
            "Answer:\u001b[32;1m\u001b[1;3m The students names are Alex, Alice, Jack, Ophelia, and Zack.\u001b[0m\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "data": {
            "text/plain": [
              "' The students names are Alex, Alice, Jack, Ophelia, and Zack.'"
            ]
          },
          "execution_count": 44,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "db_chain.run(\"What are the students names?\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 45,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
            "What's the average score of them? \n",
            "SQLQuery:\u001b[32;1m\u001b[1;3m SELECT AVG(score) FROM students;\u001b[0m\n",
            "SQLResult: \u001b[33;1m\u001b[1;3m[(Decimal('61.0000'),)]\u001b[0m\n",
            "Answer:\u001b[32;1m\u001b[1;3m The average score of them is 61.\u001b[0m\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "data": {
            "text/plain": [
              "' The average score of them is 61.'"
            ]
          },
          "execution_count": 45,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "db_chain.run(\"What's the average score of them?\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 46,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
            "What's the average score of them, excluding the zero score? \n",
            "SQLQuery:\u001b[32;1m\u001b[1;3m SELECT AVG(score) FROM students WHERE score > 0;\u001b[0m\n",
            "SQLResult: \u001b[33;1m\u001b[1;3m[(Decimal('76.2500'),)]\u001b[0m\n",
            "Answer:\u001b[32;1m\u001b[1;3m The average score of them, excluding the zero score, is 76.25.\u001b[0m\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "data": {
            "text/plain": [
              "' The average score of them, excluding the zero score, is 76.25.'"
            ]
          },
          "execution_count": 46,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "db_chain.run(\"What's the average score of them, excluding the zero score?\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 47,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
            "Who got zero score? \n",
            "SQLQuery:\u001b[32;1m\u001b[1;3m SELECT name FROM students WHERE score = 0;\u001b[0m\n",
            "SQLResult: \u001b[33;1m\u001b[1;3m[('Ophelia',)]\u001b[0m\n",
            "Answer:\u001b[32;1m\u001b[1;3m Ophelia got zero score.\u001b[0m\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "data": {
            "text/plain": [
              "' Ophelia got zero score.'"
            ]
          },
          "execution_count": 47,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "db_chain.run(\"Who got zero score?\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 48,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
            "Who got zero score? Why? \n",
            "SQLQuery:\u001b[32;1m\u001b[1;3m SELECT name, score, teacher_note FROM students WHERE score = 0;\u001b[0m\n",
            "SQLResult: \u001b[33;1m\u001b[1;3m[('Ophelia', 0, 'Unfortunately, Ophelia missed the test.')]\u001b[0m\n",
            "Answer:\u001b[32;1m\u001b[1;3m Ophelia got zero score because she missed the test.\u001b[0m\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "data": {
            "text/plain": [
              "' Ophelia got zero score because she missed the test.'"
            ]
          },
          "execution_count": 48,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "db_chain.run(\"Who got zero score? Why?\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 49,
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new SQLDatabaseChain chain...\u001b[0m\n",
            "Who got zero score? Show me her parent's contact information. \n",
            "SQLQuery:\u001b[32;1m\u001b[1;3m SELECT parents.parent_name, parents.parent_mobile FROM parents INNER JOIN students ON parents.student_name = students.name WHERE students.score = 0;\u001b[0m\n",
            "SQLResult: \u001b[33;1m\u001b[1;3m[('Tracy', '0881111111')]\u001b[0m\n",
            "Answer:\u001b[32;1m\u001b[1;3m Tracy is the parent of the student who got zero score. Her contact information is 0881111111.\u001b[0m\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "data": {
            "text/plain": [
              "' Tracy is the parent of the student who got zero score. Her contact information is 0881111111.'"
            ]
          },
          "execution_count": 49,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "db_chain.run(\"Who got zero score? Show me her parent's contact information.\")"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "authorship_tag": "ABX9TyOI6crcxsa6XS6O/5roNqo8",
      "include_colab_link": true,
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.9.16"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}
